package utils;


import cn.hutool.extra.ssh.Connector;
import cn.hutool.extra.ssh.JschUtil;

import com.jcraft.jsch.*;
import org.json.JSONArray;
import org.json.JSONObject;

import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStreamReader;
import java.net.InetSocketAddress;
import java.net.Socket;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;


public class RemoteOperation {
    private RemoteOperation() {

    }

    private static class Inner {
        private static RemoteOperation remoteOperation = new RemoteOperation();
    }

    public static RemoteOperation newInstance() {
        return Inner.remoteOperation;
    }

    /**
     * ssh 连接指定服务器
     *
     * @param host     ip地址
     * @param userName 用户名
     * @param PassWord 密码
     * @param port     端口
     * @return session 实例
     */
    private Session sshConnect(String host, String userName, String PassWord, int port) throws JSchException {
        JSch jsch = new JSch();
        Session session = jsch.getSession(userName, host, port);
        session.setPassword(PassWord);
        session.setConfig("StrictHostKeyChecking", "no");
        session.connect();

        return session;
    }

    /**
     * 在服务器执行命令
     *
     * @param host     服务器地址
     * @param userName 用户名
     * @param PassWord 密码
     * @param port     端口
     * @param command  执行命令
     * @return 执行结果
     * @throws JSchException JSchException
     * @throws IOException   IOException
     */
    public List<String> executeCommand(String host, String userName, String PassWord, int port, String command) throws JSchException, IOException {
        return execute(sshConnect(host, userName, PassWord, port), command);
    }

    /**
     * 通过跳板机链接数据库,执行增/删/改操作
     *
     * @param sshhost     跳板机地址
     * @param sshuserName 跳板机用户名
     * @param sshPassWord 跳板机密码
     * @param sshport     跳板机端口
     * @param sqlport     数据库端口
     * @param sqlhost     数据库服务器地址
     * @param sqlPassWord 密码
     * @param sqlUserName 用户
     * @param dbName      数控名称
     * @param sqlString   执行sql命令
     * @return boolean
     * @throws JSchException JSchException
     */
    public boolean executeSQL(String sshhost, String sshuserName, String sshPassWord, int sshport,
                              String sqlhost, int sqlport, String sqlUserName, String sqlPassWord,
                              String dbName, String sqlString) throws JSchException {
        Session session = sshConnect(sshhost, sshuserName, sshPassWord, sshport);
        int localport = JschUtil.generateLocalPort();
        session.setPortForwardingL(localport, sqlhost, sqlport);
        boolean result = false;

        String conn_str = "jdbc:mysql://localhost:" + localport + "/" + dbName + "?"
                + "useUnicode=true&characterEncoding=UTF8";
        try (Connection conn = DriverManager.getConnection(conn_str, sqlUserName, sqlPassWord)) {
            PreparedStatement ps = conn.prepareStatement(sqlString);
            result = ps.execute();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            session.disconnect();
        }

        return result;
    }

    /**
     * 通过ssh 链接数据库执行查询操作，返回所有查询数据
     *
     * @param sshhost     跳板机地址
     * @param sshuserName 跳板机用户名
     * @param sshPassWord 跳板机密码
     * @param sshport     ssh链接断开
     * @param sqlport     数据库端口
     * @param sqlhost     数据库服务器地址
     * @param sqlPassWord 密码
     * @param sqlUserName 用户
     * @param dbName      数控名称
     * @param sqlString   执行sql命令
     * @return 返回查询结果，并转换为JSONArray
     * @throws JSchException JSchException
     */

    public JSONArray operaterSQLAll(String sshhost, String sshuserName, String sshPassWord, int sshport,
                                    String sqlhost, int sqlport, String sqlUserName, String sqlPassWord,
                                    String dbName, String sqlString) throws JSchException {
        JSONArray jsonArray = new JSONArray();
        Session session = sshConnect(sshhost, sshuserName, sshPassWord, sshport);

        SysFreePort sysFreePort = new SysFreePort();
        int port = session.setPortForwardingL(sysFreePort.getFreePort(), sqlhost, sqlport);

        String conn_str = "jdbc:mysql://localhost:" + port + "/" + dbName + "?"
                + "user=" + sqlUserName + "&password=" + sqlPassWord + "&useUnicode=true&characterEncoding=UTF-8";
        operaterSQL(sqlString, jsonArray, session, conn_str);

        return jsonArray;
    }

    /**
     * 通过ssh 链接数据库执行查询操作，返回第一条查询数据
     *
     * @param sshhost     跳板机地址
     * @param sshuserName 跳板机用户名
     * @param sshPassWord 跳板机密码
     * @param sshport     ssh链接断开
     * @param sqlport     数据库端口
     * @param sqlhost     数据库服务器地址
     * @param sqlPassWord 密码
     * @param sqlUserName 用户
     * @param dbName      数控名称
     * @param sqlString   执行sql命令
     * @return 返回查询结果，并转换为JSONArray
     * @throws JSchException JSchException
     */
    public List<Object> operaterSQL(String sshhost, String sshuserName, String sshPassWord, int sshport,
                                    String sqlhost, int sqlport, String sqlUserName, String sqlPassWord,
                                    String dbName, String sqlString) throws JSchException {
        List<Object> list = new ArrayList<>();
        Session session = sshConnect(sshhost, sshuserName, sshPassWord, sshport);

        SysFreePort sysFreePort = new SysFreePort();
        int port = session.setPortForwardingL(sysFreePort.getFreePort(), sqlhost, sqlport);

        String conn_str = "jdbc:mysql://localhost:" + port + "/" + dbName + "?"
                + "user=" + sqlUserName + "&password=" + sqlPassWord + "&useUnicode=true&characterEncoding=UTF-8";
        operaterSQL(sqlString, list, session, conn_str);

        return list;
    }

    private List<String> execute(Session session, String command) throws JSchException, IOException {
        List<String> list = new ArrayList<>();
        Channel channel = session.openChannel("exec");


        ((ChannelExec) channel).setCommand(command);
//        channel.setInputStream(null);
        BufferedReader input = new BufferedReader(new InputStreamReader(channel
                .getInputStream()));
        channel.connect();

        System.out.println("The remote command is: " + command);

        // Get the output of remote command.
        String line;
        while ((line = input.readLine()) != null) {
            list.add(line);
        }
        input.close();

        // Get the return code only after the channel is closed.
        if (channel.isClosed()) {
            channel.getExitStatus();
        }
        channel.disconnect();
        session.disconnect();
        return list;
    }

    /**
     * 在开发服务器执行命令
     *
     * @param command linux 命令
     * @return 结果
     */
    public List<String> executeCommand(String command) throws IOException, JSchException {
        return RemoteOperation.newInstance().executeCommand(Config.SSHHOST, Config.SSHUSERNAME
                , Config.SSHPASSWORD, Config.SSHPORT, command);
    }

    /**
     * 在测试服务器 ，指定数据库执行 增删改命令
     *
     * @param dbName     数据库名称
     * @param sqlcommand 操作命令
     */
    public boolean executeSQL(String dbName, String sqlcommand) throws JSchException {
        return RemoteOperation.newInstance().executeSQL(Config.SSHHOST, Config.SSHUSERNAME
                , Config.SSHPASSWORD, Config.SSHPORT, Config.SQLHOST, Config.SQLPORT,
                Config.SQLUSERNAME, Config.SQLPASSWORD, dbName, sqlcommand);
    }

    /**
     * 在数据库执行查询语句，返回所有结果
     *
     * @param dbName     数据库名称
     * @param sqlcommand 执行命令
     * @return JSONArray
     */
    public JSONArray selectSQLAll(String dbName, String sqlcommand) throws JSchException {
        return RemoteOperation.newInstance().operaterSQLAll(Config.SSHHOST, Config.SSHUSERNAME
                , Config.SSHPASSWORD, Config.SSHPORT, Config.SQLHOST, Config.SQLPORT,
                Config.SQLUSERNAME, Config.SQLPASSWORD, dbName, sqlcommand);
    }

    public List<Object> selectSQL(String dbName, String sqlcommand) throws JSchException {
        return RemoteOperation.newInstance().operaterSQL(Config.SSHHOST, Config.SSHUSERNAME
                , Config.SSHPASSWORD, Config.SSHPORT, Config.SQLHOST, Config.SQLPORT,
                Config.SQLUSERNAME, Config.SQLPASSWORD, dbName, sqlcommand);
    }

    /**
     * 将查询的所有数据封装到json数组返回
     */
    private void operaterSQL(String sqlString, JSONArray jsonArray, Session session, String conn_str) {
        System.out.println("执行sql语句：" + sqlString);
        try (Connection conn = DriverManager.getConnection(conn_str); Statement stmt = conn.createStatement()) {
            ResultSet result = stmt.executeQuery(sqlString);
            if (result != null) {
                ResultSetMetaData rsm = result.getMetaData();
                int col = rsm.getColumnCount();//列数

                while (result.next()) {
                    JSONObject jsonObject = new JSONObject();
                    for (int i = 1; i <= col; i++) {
                        jsonObject.put(rsm.getColumnName(i), result.getObject(i));//getColumnName 获取当前列的字段名
                    }
                    jsonArray.put(jsonObject);
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            session.disconnect();
        }
    }

    /**
     * 默认返回第一列的所有数据
     */
    private void operaterSQL(String sqlString, List<Object> list, Session session, String conn_str) {
        System.out.println("执行sql语句：" + sqlString);
        Statement stmt;
        try (Connection conn = DriverManager.getConnection(conn_str)) {
            stmt = conn.createStatement();
            ResultSet result = stmt.executeQuery(sqlString);
            if (result != null) {
                while (result.next()) {
                    list.add(result.getString(1));//将第一列的所有数据加入列表，并返回
                }
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            session.disconnect();
        }
    }

    static class SysFreePort {

        Socket socket;

        public SysFreePort() {

            socket = new Socket();
            InetSocketAddress inetSocketAddress = new InetSocketAddress(0);
            try {
                socket.bind(inetSocketAddress);
            } catch (IOException e) {
                e.printStackTrace();
            }
        }

        public int getFreePort() {
            int port = socket.getLocalPort();
            try {
                socket.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
            return port;
        }
    }
}
